﻿using System;
using System.Collections.Generic;
using System.Text;
using Com.Toook.ParseHtml.Entity;
using Com.Toook.Common;
using System.Collections.Specialized;
using System.Collections;
using System.Data;
using System.Data.OleDb;

namespace Com.Toook.ParseHtml
{
    public class SaveResultToAccess
    {
        /// <summary>
        /// 保存分类页面中的Url等结果到SiteKindPageUrlTemp***表中(表名在xml在配置)。如果url已经存在返回true, 不存在返回FALSE
        /// </summary>
        /// <param name="result"></param>
        /// <returns>如果url已经存在返回true, 不存在返回FALSE</returns>
        public static bool SaveKindUrl(ResultKind result)
        {
            bool existSameUrl = true;
            
            //year是Access保留字要加[year]
            string command = "insert into " + result.SaveToTableName + " ([url], [title], [summary], [img], siteKindId, kindUrl, author, actor, [year], area ) "
                //+ " values(@url, @title, @summary, @img, @siteKindId, @kindUrl, @author, @actor, @year, @area) ";
                + " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
            OleDbConnection con = AccessHelper.GetConnection();
            OleDbCommand cmd = new OleDbCommand(command, con);
            cmd.Parameters.Add("url", OleDbType.VarChar, 200).Value = result.Url;
            cmd.Parameters.Add("title", OleDbType.VarWChar, 40).Value = result.Title;
            cmd.Parameters.Add("summary", OleDbType.VarWChar, 200).Value = result.Summary;
            cmd.Parameters.Add("img", OleDbType.VarChar, 200).Value = result.Img;
            cmd.Parameters.Add("siteKindId", OleDbType.VarWChar, 20).Value = result.SiteKindId;
            cmd.Parameters.Add("kindUrl", OleDbType.VarWChar, 200).Value = result.KindPageUrl;
            cmd.Parameters.Add("author", OleDbType.VarWChar, 20).Value = result.Author;
            cmd.Parameters.Add("actor", OleDbType.VarWChar, 20).Value = result.Actor;
            cmd.Parameters.Add("year", OleDbType.VarWChar, 4).Value = result.Year;
            cmd.Parameters.Add("area", OleDbType.VarWChar, 20).Value = result.Area;
            //Access中number是保留字，并且别名必须加as
            OleDbCommand existCmd = new OleDbCommand("select count(*) as num from " + result.SaveToTableName + " where url=?", con);
            existCmd.Parameters.Add("url", OleDbType.VarChar, 200).Value = result.Url;
            try
            {
                con.Open();
                int number = int.Parse(existCmd.ExecuteScalar().ToString());
                if (number == 0)
                {
                    existSameUrl = false;//不存在相同url的记录
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log.Error("SaveKindUrl保存结果错误", ex);
            }
            finally
            {
                AccessHelper.CloseDbResource(con, null);
            }

            return existSameUrl;
        }
        /// <summary>
        /// 从SiteKindPageUrlTemp表中获取某个分类新增的页面url、标题等
        /// </summary>
        /// <param name="siteKindId"></param>
        /// <param name="detailTableName">保存详细内容的表名，即没有Temp的表名</param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public static DataTable GetKindUrlTemp(string siteKindId, string detailTableName, string fields)
        {
            fields = string.IsNullOrEmpty(fields) ? "url" : fields;//如果指定从数据库获取字段值，在字段名称前面加上逗号
            string command = "select " + fields
                + " from " + detailTableName + "Temp where isGetDetail=0 and siteKindId='" + siteKindId + "' ";            
            OleDbConnection con = AccessHelper.GetConnection();          
            OleDbDataAdapter da = new OleDbDataAdapter(command, con);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        /// <summary>
        /// 直接从RssNews表读取新闻link
        /// </summary>
        /// <param name="siteId">RssSites中的SiteId</param>
        /// <param name="detailTableName"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public static DataTable GetRssNewsUrl(int siteId, string detailTableName, string fields)
        {
            string command = "select id,link,content from " + detailTableName + " where isGetContent=0 and siteId=" + siteId.ToString();
            OleDbConnection con = AccessHelper.GetConnection();           
            OleDbDataAdapter da = new OleDbDataAdapter(command, con);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }
        /// <summary>
        /// 保存最终内容到数据库
        /// </summary>
        /// <param name="siteKindId">站点分类ID</param>
        /// <param name="tableName">插入表名</param>
        /// <param name="finalcontent">多个分页拼合的内容</param>
        /// <param name="dicResult">其他属性</param>
        public static void SaveFinalContent(string siteKindId, string tableName, string finalcontent, StringDictionary dicResult, string startDetailUrl)
        {
            string command = "INSERT INTO " + tableName + " ( ";
            string nameField = "siteKindId";//, url
            string valueField = "@siteKindId";//, @url
            string key;
            OleDbConnection con = AccessHelper.GetConnection();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.Parameters.Add("@siteKindId", OleDbType.VarChar, 20).Value = siteKindId;

            if(!string.IsNullOrEmpty(finalcontent))
            {
                nameField += ",content";
                valueField += ",@content";

                cmd.Parameters.Add("@content", System.Data.DbType.String).Value = finalcontent; 
            }
            foreach (DictionaryEntry field in dicResult)
            {
                key = field.Key.ToString();
                nameField += "," + key;
                valueField += ",@" + key;

                cmd.Parameters.Add("@" + key, System.Data.DbType.String).Value = field.Value.ToString(); 
            }
            command = command + nameField + ") values (" + valueField + ")";//插入内容
            string updateCmd = "update " + tableName + "Temp set  isGetDetail=1 where url=@url ";//更新Temp,标记为已经获取内容//清空字段title='完', summary=null, img=null, author=null, actor=null,
            cmd.CommandText = command;

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                //将SiteKindPageUrlTemp表对应的url记录标记为已经获取内容isGetDetail=1
                cmd.CommandText = updateCmd;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@url", OleDbType.VarChar, 200).Value = startDetailUrl;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogHelper.Log.Error("保存最终内容失败. table=" + tableName, ex);
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// 保存RSS新闻内容
        /// </summary>
        /// <param name="id"></param>
        /// <param name="tableName"></param>
        /// <param name="content"></param>
        /// <param name="dicResult"></param>
        public static void SaveRssNewsContent(string id, string tableName, bool isGetContentSuccess, StringDictionary dicResult)
        {
            string command = null;
            if (isGetContentSuccess)//获取内容成功
            {
                command = "update " + tableName + " set isGetContent=1, haveContent=1, createTime=@createTime, content=@content where id=@id";
            }
            else//没有获取到内容
            {
                command = "update " + tableName + " set isGetContent=1, haveContent=0, createTime=@createTime where id=@id";
            }
            OleDbConnection con = AccessHelper.GetConnection();
            OleDbCommand cmd = new OleDbCommand(command, con);
            cmd.Parameters.Add("@createTime", OleDbType.Date).Value = DateTime.Now;
            cmd.Parameters.Add("@id", OleDbType.VarWChar, 20).Value = id;

            string key;
            if (isGetContentSuccess)
            {
                foreach (DictionaryEntry field in dicResult)
                {
                    key = field.Key.ToString();
                    if (key == "content")
                    {
                        cmd.Parameters.Add("@content", OleDbType.LongVarWChar).Value = field.Value.ToString();
                        break;
                    }
                }
            }
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();                
            }
            catch (Exception ex)
            {
                LogHelper.Log.Error("保存最终内容失败. table=" + tableName, ex);
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// 保存顺序获取的每个“下一页”内容
        /// </summary>
        /// <param name="url">来源url</param>
        /// <param name="tableName">插入表名</param>
        /// <param name="dicResult">所有内容</param>
        public static bool SaveNextPageArticleContent(string url, string tableName, StringDictionary dicResult)
        {
            bool isSuccess = true;
            string command = "INSERT INTO " + tableName + " ( ";
            string nameField = "url";
            string valueField = "@url";
            string key;
            OleDbConnection con = AccessHelper.GetConnection();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.Parameters.Add("@url", OleDbType.VarChar, 200).Value = url;
            //生成其他字段参数
            foreach (DictionaryEntry field in dicResult)
            {                
                key = field.Key.ToString();
                nameField += "," + key;
                valueField += ",@" + key;
                if (field.Value == null)
                {
                    isSuccess = false;
                    LogHelper.Log.Warn("field." + field.Key.ToString() + "=null, url=" + url );
                    return isSuccess;
                }
                else
                {
                    cmd.Parameters.Add("@" + key, System.Data.DbType.String).Value = field.Value.ToString();
                }
            }
            command = command + nameField + ") values (" + valueField + ")";//插入内容            
            cmd.CommandText = command;

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();                
            }
            catch (Exception ex)
            {
                LogHelper.Log.Error("保存最终内容失败. table=" + tableName, ex);
                isSuccess = false;
            }
            finally
            {
                con.Close();
            }

            return isSuccess;
        }
        /// <summary>
        /// 是否已经获取了相同url的文章内容
        /// </summary>
        /// <param name="url"></param>
        /// <returns></returns>
        public static bool HaveSameArticle(string url, string tableName)
        {
            string haveCmd = "SELECT count(url) AS linkCount FROM " + tableName + " WHERE  url=?";
            OleDbConnection conhave = AccessHelper.GetConnection();
            OleDbCommand cmdhave = new OleDbCommand(haveCmd, conhave);
            cmdhave.Parameters.Add("url", OleDbType.VarChar, 200).Value = url;
            int counter = 1;
            try
            {
                conhave.Open();
                counter = (int)cmdhave.ExecuteScalar();
            }
            finally
            {
                conhave.Close();
            }

            return counter > 0;
        }
        /// <summary>
        /// 保存搜索引擎之外的其他采集机器人记录
        /// </summary>
        /// <param name="robot"></param>
        public static void SaveRobotVisitRecord(Robots robot)
        {
            string sql = "insert into RobotVisitRecord(ip, agent, robot) " +
                " values(@ip, @agent, @robot)";
            OleDbConnection con = AccessHelper.GetConnection();
            OleDbCommand cmd = new OleDbCommand(sql, con);
            cmd.Parameters.Add("@ip", OleDbType.VarChar, 20).Value = robot.VisitIp;
            cmd.Parameters.Add("@agent", OleDbType.VarChar, 250).Value = robot.VisitAgent;
            cmd.Parameters.Add("@robot", OleDbType.LongVarChar).Value = robot.ToString();

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                LogHelper.Log.Error("SaveRobotVisitRecord错误。ip=" + robot.VisitIp + ", agent=" + robot.VisitAgent + robot.ToString(), ex);
            }
            finally
            {
                con.Close();
            }
        }
    }
}
